package com.psl.services.work.list;

import com.psl.Dao.Conn;
import com.psl.model.moneyList;
import org.apache.struts2.ServletActionContext;

import javax.servlet.http.HttpServletRequest;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class spendmoney {
    public boolean insert(String name, int num, String unit, float money, int type, String date) {
        Connection conn = new Conn().getConn();
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement("insert into yihaopinlist(name,num,unit,money,type,date) VALUES (?,?,?,?,?,?)");
            pstmt.setString(1, name);
            pstmt.setInt(2, num);
            pstmt.setString(3, unit);
            pstmt.setFloat(4, money);
            pstmt.setInt(5, type);
            pstmt.setString(6, date);
            pstmt.execute();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();

        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return true;
    }


    public List moneylistAll() {
        Connection conn = new Conn().getConn();
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        List money = new ArrayList();
        String sql1 = "SELECT * FROM yihaopinlist ";
        String sql2 = " ORDER by id DESC";
        try {
            pstmt = conn.prepareStatement(sql1 + sql2);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                moneyList ml = new moneyList();
                ml.setId(rs.getInt(1));
                ml.setName(rs.getString(2));
                ml.setNum(rs.getInt(3));
                ml.setUnit(rs.getString(4));
                ml.setMoney(rs.getFloat(5));
                ml.setType(rs.getString(6));
                ml.setDate(rs.getString(7));
                money.add(ml);
                HttpServletRequest request = ServletActionContext.getRequest();
                request.setAttribute("money", money);
            }
            return money;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (rs != null) {
                    rs.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    public boolean del(int id) {
        Connection conn = new Conn().getConn();
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement("DELETE from  yihaopinlist where id=?");
            pstmt.setInt(1, id);
            pstmt.executeUpdate();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return true;
    }
}
